ALTER PROCEDURE SP_CREATE_UPDATE_RMA_RECEIVE_LOT (

@CUSTOMER_ID	INT,
@RMA_RECEIVE_ID INT,
@RMA_RECEIVE_NO VARCHAR(50),
@RMA_RECEIVE_LOT_ID INT,
@PROCESSSTEP_ID INT,
@LOCATION_ID    INT,
@LOT_NUMBER     VARCHAR(50),
@FAB_LOT_NUMBER VARCHAR(50),
@DIE_QTY_IN		INT,
@DATE_IN		DATETIME,
@COMMENTS		TEXT,
@IS_CLOSE		INT,
@USER_NAME		VARCHAR(50),
@DATE_CODE		VARCHAR(20)

)
AS
BEGIN		
		DECLARE @RMA_REC_LOT_ID INT
		DECLARE @RMA_REC_ID INT

		IF @RMA_RECEIVE_ID IS NOT NULL AND @RMA_RECEIVE_ID > 0
		BEGIN
	
			UPDATE RMA_RECEIVE_TABLE SET RMA_STATUS = 'Receive', USER_NAME = @USER_NAME WHERE RMA_RECEIVE_ID = @RMA_RECEIVE_ID
			SET @RMA_REC_ID = @RMA_RECEIVE_ID
			
		END
		ELSE
		BEGIN
				SELECT @RMA_REC_ID = RMA_RECEIVE_ID FROM RMA_RECEIVE_TABLE WHERE CUSTOMER_ID = @CUSTOMER_ID AND RMA_RECEIVE_NUMBER = @RMA_RECEIVE_NO

				IF @RMA_REC_ID IS NULL
				BEGIN
					INSERT INTO RMA_RECEIVE_TABLE (CUSTOMER_ID, RMA_RECEIVE_NUMBER,RMA_STATUS,USER_NAME, TIMESTAMP)
						VALUES ( @CUSTOMER_ID, @RMA_RECEIVE_NO, 'Receive', @USER_NAME, CURRENT_TIMESTAMP)
					SELECT @RMA_REC_ID = @@IDENTITY
			
				END
				ELSE
				BEGIN
						UPDATE RMA_RECEIVE_TABLE SET RMA_STATUS = 'Receive', USER_NAME = @USER_NAME WHERE RMA_RECEIVE_ID = @RMA_REC_ID
				END
		END

		IF @RMA_RECEIVE_LOT_ID IS NOT NULL AND @RMA_RECEIVE_LOT_ID > 0
		BEGIN
				IF @IS_CLOSE > 0
				BEGIN
					UPDATE RMA_RECEIVE_LOT_TABLE SET PROCESSSTEP_ID = @PROCESSSTEP_ID, LOCATION_ID = @LOCATION_ID, LOT_NUMBER = @LOT_NUMBER, FAB_LOT_NUMBER = @LOT_NUMBER, QTY_IN = @DIE_QTY_IN, DATE_IN = @DATE_IN, DATE_CODE = @DATE_CODE, TIMESTAMP = CURRENT_TIMESTAMP
						 WHERE RMA_RECEIVE_LOT_ID = @RMA_RECEIVE_LOT_ID
				END
				ELSE
				BEGIN
					UPDATE RMA_RECEIVE_LOT_TABLE SET STATUS = 'Close', TIMESTAMP = CURRENT_TIMESTAMP WHERE RMA_RECEIVE_LOT_ID = @RMA_RECEIVE_LOT_ID
				END
		END
		ELSE
		BEGIN
				SELECT @RMA_REC_LOT_ID = RMA_RECEIVE_LOT_ID FROM RMA_RECEIVE_LOT_TABLE WHERE RMA_RECEIVE_ID = @RMA_REC_ID AND PROCESSSTEP_ID = @PROCESSSTEP_ID AND  LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER
				IF @RMA_REC_LOT_ID IS NULL
				BEGIN
						INSERT INTO RMA_RECEIVE_LOT_TABLE ( RMA_RECEIVE_ID, PROCESSSTEP_ID, LOCATION_ID,LOT_NUMBER,FAB_LOT_NUMBER,QTY_IN,DATE_IN,COMMENTS, DATE_CODE, TIMESTAMP, STATUS)
							VALUES ( @RMA_REC_ID, @PROCESSSTEP_ID, @LOCATION_ID,@LOT_NUMBER,@LOT_NUMBER,@DIE_QTY_IN,@DATE_IN, @COMMENTS, @DATE_CODE, CURRENT_TIMESTAMP,'Receive')
				END
				ELSE
				BEGIN
						UPDATE RMA_RECEIVE_LOT_TABLE SET PROCESSSTEP_ID = @PROCESSSTEP_ID, LOCATION_ID = @LOCATION_ID, LOT_NUMBER = @LOT_NUMBER, FAB_LOT_NUMBER = @LOT_NUMBER, QTY_IN = @DIE_QTY_IN, DATE_IN = @DATE_IN, DATE_CODE = @DATE_CODE, TIMESTAMP = CURRENT_TIMESTAMP
									WHERE RMA_RECEIVE_LOT_ID = @RMA_REC_LOT_ID
				END
		END
END